package it.univaq.mwt.arssa.business.impl;

import it.univaq.mwt.arssa.business.BusinessException;
import it.univaq.mwt.arssa.business.SediService;
import it.univaq.mwt.arssa.business.model.Sede;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

public class JDBCSediService implements SediService {

	private DataSource dataSource;
	
	public JDBCSediService(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public List<Sede> cercaSedi() {
		
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<Sede> result = new ArrayList<Sede>();
		
		try {
			con = dataSource.getConnection();
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM Sedi ORDER BY ID DESC");
			while (rs.next()) {
				int id = rs.getInt("id");
				String codice = rs.getString("codice");
				String luogo = rs.getString("luogo");
				String festaPatronale = rs.getString("festaPatronale");
				
				result.add(new Sede(id, codice, luogo, festaPatronale));
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return result;
		
	}

	@Override
	public Sede cercaSede(int id) {
		
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Sede result = null;
		
		try {
			
			con = dataSource.getConnection();
			st = con.prepareStatement("SELECT * FROM Sedi WHERE id=?");
			st.setInt(1, id);
			rs = st.executeQuery();
			
			if (rs.next()) {
				String codice = rs.getString("CODICE");
				String luogo = rs.getString("LUOGO");
				String festaPatronale = rs.getString("FESTAPATRONALE");
				
				result = new Sede(id, codice, luogo, festaPatronale);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return result;
	}

	@Override
	public void inserisciSede(Sede sede) {
		
		Connection con = null;
		PreparedStatement st = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("INSERT INTO SEDI (id, codice, luogo, festaPatronale) VALUES (NULL,?,?,?)");
			st.setString(1, sede.getCodice());
			st.setString(2, sede.getLuogo());
			st.setString(3, sede.getFestaPatronale());
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	@Override
	public void modificaSede(Sede sede) {

		Connection con = null;
		PreparedStatement st = null;
		
		try {					
			con = dataSource.getConnection();				
			st = con.prepareStatement("UPDATE SEDI SET CODICE=?, LUOGO=?, FESTAPATRIMONIALE=?  WHERE (ID=?)");		
			st.setString(1, sede.getCodice());
			st.setString(2, sede.getLuogo());
			st.setString(3, sede.getFestaPatronale());
			st.setInt(4, sede.getId());
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		
	}

	@Override
	public void eliminaSede(int id) {
		
		Connection con = null;
		PreparedStatement st = null;
		
		try {					
			con = dataSource.getConnection();
			st = con.prepareStatement("DELETE FROM SEDI WHERE (ID=?)");		
			st.setInt(1, id);
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		
	}
	
	@Override
	public boolean verificaCodice(String codice) {

		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("SELECT * FROM SEDI WHERE CODICE=?");
			st.setString(1, codice);
			rs = st.executeQuery();
			
			if(rs.next()){
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return false;
		
	}
		

}
